﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.SQLite;


namespace FixtureManageSystem.Models
{
    public class FixtureService
    {
        // 显示的夹具列表
        public static List<Fixture> _fixtures;

        public SqliteHelper sqliteHelper;
        // 构造函数
        public FixtureService()
        {
            sqliteHelper = new SqliteHelper();
        }

        // 获取物料的总数。应该要加上条件吧, 
        public int GetFixturesCount()
        {
            // 创建一变量来存储返回值
            int count;
            // 1、创建要执行的Sql 语句
            string str = "SELECT COUNT(*) as totalNumber FROM Fixture;";  
            // 2、执行SQL语句并接受结果
            SQLiteDataReader Sdr = sqliteHelper.read(str);

            // 3、读取结果的数据
            while (Sdr.Read())
            {
                count = Convert.ToInt32(Sdr["totalNumber"]);
                return count;
            }
            return 0;
        }


        /// <summary>
        /// 查询第 page 页，pagesize数量的条目，其实仅需改变列表_fixtures就行
        /// </summary>
        public ObservableCollection<Fixture> InquireFixtures(int page, int pageSize)
        {

            ObservableCollection<Fixture> _fixtures = new ObservableCollection<Fixture>();
            // 创建要执行的Sql语句
            string str = $"select * from Fixture  Limit {pageSize} OFFSET {(page - 1) * pageSize};";
            // 2、执行SQL语句并接受结果
            SQLiteDataReader Sdr = sqliteHelper.read(str);
            // 4、读取结果的数据
            while (Sdr.Read())
            {
                _fixtures.Add(new Fixture()
                {
                    BarCode = Sdr["BarCode"].ToString(),
                    Name = Sdr["Name"].ToString(),
                    MaterialType = Sdr["MaterialType"].ToString(),
                    Num = Convert.ToInt32(Sdr["Num"]),
                    Position = Sdr["Position"].ToString()
                });
            }
            return _fixtures;
        }

        public ObservableCollection<Fixture> Search(Fixture em)
        {
            ObservableCollection<Fixture> result = new ObservableCollection<Fixture>();
            string barcode = em.BarCode;
            string name = em.Name;
            string materialType = em.MaterialType;

            // 创建要执行的Sql 语句
            string str = $"select * from Fixture Where ";
            List<string> conditions = new List<string>();
            if (!string.IsNullOrEmpty(barcode))
            {
                conditions.Add($"Barcode = '{barcode}' ");
            }
            if (!string.IsNullOrEmpty(name))
            {
                conditions.Add($"Name Like '%{name}%' ");
            }
            if (!string.IsNullOrEmpty(materialType))
            {
                conditions.Add($"MaterialType Like '{materialType}' ");
            }
            if (conditions.Count == 0)
            {
                return result;
            }
            str += string.Join(" AND ", conditions);

            SQLiteDataReader Sdr = sqliteHelper.read(str);

            int count = 0;
            // 4、读取结果的数据
            while (Sdr.Read())
            {
                result.Add(new Fixture()
                {
                    BarCode = Sdr["BarCode"].ToString(),
                    Name = Sdr["Name"].ToString(),
                    MaterialType = Sdr["MaterialType"].ToString(),
                    Position = Sdr["Position"].ToString(),
                    Num = Convert.ToInt32(Sdr["Num"]),
                });
                count += 1;
            }
            return result;
        }
    }
}
